package repositories

import (
	"database/sql"
	"fmt"
	"gcopyright/models"
	"log"
)

func checkErr(err error) {
	if err != nil {
		fmt.Println("Prepare Error  ", err)
	}
}

type RepositoriesSqlite struct {
	sqlite *sql.DB
}

func NewRepositoriesSqlite() *RepositoriesSqlite {
	return &RepositoriesSqlite{sqlite: models.SQLITE.Sqlite3}
}

// Ping sql
func (r *RepositoriesSqlite) Ping() {
	if err := r.sqlite.Ping(); err != nil {
		log.Fatal(err)
	}
	fmt.Println("PING OK")
}

// 创建表 userinfo 表
func (r *RepositoriesSqlite) CreateTable() {
	sql := r.sqlite
	sqlStmt := `
	create table userinfo (username text , password text);
	`
	_, err := sql.Exec(sqlStmt)
	checkErr(err)
	//fmt.Println(f)
}

/*
type AccountsTable struct {
	Accounts string `json:"accounts"`
	Password string `json:"password"`
	Balance  string `json:"balance"`
}
type EtherTable struct {
	Accounts       string `json:"accounts"`
	DeployAddress string `json:"deploy_address"`
	Content       string `json:"content"`
	Hash          string `json:"hash"`
	Timestamp	  string `json:"timestamp"`
	Author    	  string `json:"author"`
}*/
// 创建 AccountsTable
func (r *RepositoriesSqlite) CreateAccountsTable() {
	sql := r.sqlite
	sqlStmt := `
	create table accounts_table (account text , password text ,  number int );
	`
	_, err := sql.Exec(sqlStmt)
	checkErr(err)
}

// 删除表 accounts_Table
func (r *RepositoriesSqlite) DeleteTable() {
	sql := r.sqlite
	sqlStmt := `
	DROP TABLE accounts_Table;
	`
	_, err := sql.Exec(sqlStmt)
	checkErr(err)
}

// accounts_table 插入数据
func (r *RepositoriesSqlite) InsertAccountsTable(account, password string, number int) {
	sql := r.sqlite
	stmt, err := sql.Prepare("INSERT INTO accounts_table(account, password , number) values(?,?,?)")
	checkErr(err)
	res, err := stmt.Exec(account, password, number)
	checkErr(err)
	_, err = res.LastInsertId()
	checkErr(err)

}

// 查询测试数据数据
func (r *RepositoriesSqlite) LoginQuery(result string) models.AccountsTable {
	sql := r.sqlite
	rows, err := sql.Query("SELECT * FROM accounts_table WHERE account = ?", result)
	checkErr(err)
	var user = models.AccountsTable{}
	for rows.Next() {
		err = rows.Scan(&user.Account, &user.Password, &user.Number)
		checkErr(err)
	}
	return user
}

// 删除数据

func (r *RepositoriesSqlite) Delete(s string) {
	sql := r.sqlite
	stmt, err := sql.Prepare("DELETE  FROM userinfo WHERE username = ?")
	if err != nil {
		log.Fatal(err)
	}
	result, err := stmt.Exec(s)
	affectNum, err := result.RowsAffected()
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println("delete affect rows is ", affectNum)

}

// 数据更新

func (r *RepositoriesSqlite) Update() {
	sql := r.sqlite
	stmt, err := sql.Prepare("UPDATE userinfo SET created = ? WHERE username = ?")
	if err != nil {
		log.Fatal(err)
	}
	result, err := stmt.Exec("2016-09-7", "张书林")
	affectNum, err := result.RowsAffected()
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println("update affect rows is ", affectNum)
}
